package com.suoluo.zenadmin.dao.trip;

import java.util.Date;
import java.util.List;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;

import com.suoluo.zenadmin.vo.dest.DestFeatureItem2VO;
import com.suoluo.zenadmin.vo.dest.DestTraffic2VO;
import com.suoluo.zenadmin.vo.dest.DestVO;
import com.suoluo.zenadmin.vo.friend.FriendItemVO;
import com.suoluo.zenadmin.vo.trip.TripDailyFormVO;
import com.suoluo.zenadmin.vo.trip.TripDailyLocationsVO;
import com.suoluo.zenadmin.vo.trip.TripDailyVO;
import com.suoluo.zenadmin.vo.trip.TripFlightVO;
import com.suoluo.zenadmin.vo.trip.TripLocationVO;
import com.suoluo.zenadmin.vo.trip.TripTripdestsVO;
import com.suoluo.zenadmin.vo.trip.TripVO;
import com.suoluo.zenadmin.vo.trip.TripdestVO;

@DAO
public interface TripDAO {

	@SQL("select * from tb_trip where trip_id = :1")
	public TripVO findTripByTripId(int tripId);

	@SQL("update tb_trip set status = 1 where trip_id = :1")
	public void updateTripEnable(int tripId);
	
	/***************************改版行程的DAO***************************************/
	@SQL("select * from tb_tripdest  " 
			+ "#if(:destName != null){ where name like :destName } "+
			" order by tripdest_id desc limit :1,:2")
	public List<TripdestVO> findListTripdest(int start,int rows,@SQLParam("destName")String destName);
	
	@SQL("select * from tb_tripdest where tripdest_id = :1")
	public TripdestVO findTripdestById(int tripdestId);
	
	@SQL("update tb_tripdest set name=:1.name,lat=:1.lat,lng=:1.lng,status=:1.status where tripdest_id=:1.tripdestId")
	public void updateTripdest(TripdestVO tripdest);
	/**
	 * 查询行程目的地中的最大的系统目的地ID,因为自定义目的地的目的地ID是0，所以直接查询tb_tripdests目的地的dest_id
	 */
	@SQL("select max(dest_id) from tb_tripdest")
	public Integer findMaxSysDestId();
	
	/**
	 * 添加行程目的地
	 * @param tripdest
	 */
	@ReturnGeneratedKeys
	@SQL("insert into tb_tripdest (name, dest_id,lat,lng,custom,status,image,google_id,google_reference) " +
			"values (:1.name,:1.destId,:1.lat,:1.lng,:1.custom,:1.status,:1.image,:1.googleId,:1.googleReference)")
	public int addTripdests(TripdestVO tripdest);
	
	/**
	 * 查询剩余的系统目的地
	 * @param maxDestId 该目的地后面的剩余目的地
	 * @param pageCount 查询剩余的记录
	 * @return
	 */
	@SQL("select * from tb_dest t1 join tb_image t2 on t1.image_id = t2.image_id where dest_id > :1 limit 0,:2")
	public List<DestVO> findRemainSysDests(int maxDestId,int pageCount);
	
	/**
	 * 查询行程目的地下的最大系统地点的数据ID
	 */
	@SQL("select max(data_id) from tb_trip_location where tripdest_id = :1 and custom = 0")
	public Integer findLocationMaxDataId(int tripdestId);
	
	/**
	 * 查询行程目的地下的最大系统酒店的的数据ID
	 */
	@SQL("select max(data_id) from tb_trip_location where tripdest_id = :1 and custom = 0 and category_id = 3")
	public Integer findHotelMaxDataId(int tripdestid);
	
	/**
	 * 添加行程目的地
	 * @param location
	 * @return
	 */
	@ReturnGeneratedKeys
	@SQL("insert into tb_trip_location (name, content,summary,lat,lng,image,stay_time,category_id,category_name,tripdest_id,dest_name,dest_id,data_id,rel_id,status,custom,google_id,google_reference) " +
			"values (:1.name,:1.content,:1.summary,:1.lat,:1.lng,:1.image,:1.stayTime,:1.categoryId,:1.categoryName,:1.tripdestId,:1.destName,:1.destId,:1.dataId,:1.relId,:1.status,:1.custom,:1.googleId,:1.googleReference)")
	public int addTripLocation(TripLocationVO location);
	
	
	/**
	 * 获取目的地下剩余的特色项
	 * @param destId
	 * @param maxDataId
	 * @return
	 */
	@SQL("select * from tb_dest_feature_item2 t1 join tb_base_info t2 on t2.data_id=t1.feature_item_id "
			+ "and t2.info_type=:3 join tb_image t3 on t3.image_id = t1.image_id "
			+ "where t1.dest_id = :1 and t1.feature_item_id > :2 and t1.feature_category_id != 5 and t2.default_info=1")
	public List<DestFeatureItem2VO> findRemainFeatureItems(int destId,int maxDataId,int infoType);
	
	/**
	 * 获取目的地下剩余的酒店
	 * @param destId
	 * @param maxDataId
	 * @return
	 */
	@SQL("select * from tb_dest_feature_item2 t1 join tb_base_info t2 on t2.data_id=t1.rel_id "
			+ "and t2.info_type=:3 join tb_image t3 on t3.image_id = t1.image_id "
			+ "where t1.dest_id = :1 and t1.feature_item_id > :2 and t1.feature_category_id = 3 and t2.default_info=1")
	public List<DestFeatureItem2VO> findRemainHotels(int destId,int maxDataId,int infoType);
	
	/**
	 * 查询行程目的地下审核通过的地点
	 * @param tripdestId
	 * @param categoryId
	 * @return
	 */
	@SQL("select * from tb_trip_location where tripdest_id = :1 and category_id = :2 and status = 1")
	public List<TripLocationVO> findTripLocatons(int tripdestId,int categoryId);
	
	@SQL("select * from tb_trip_location where tripdest_id = :1  " +
			"#if(:locationName != null){ and name like :locationName }" +
			" order by location_id desc limit :2,:3")
	public List<TripLocationVO> findListTripLocation(int tripdestId, int start,
			int rows,@SQLParam("locationName")String locationName);
	
	
	@SQL("select count(*) from tb_trip_location where tripdest_id =:1 " +
			"#if(:locationName != null){ and name like :locationName }")
	public int findTripLocationCount(int tripdestId,@SQLParam("locationName")String locationName);
	
	
	@SQL("select * from tb_trip_location where location_id = :1")
	public TripLocationVO findTripLocationById(int locationId);
	
	
	@SQL("update tb_trip_location set name=:1.name,content=:1.content,summary=:1.summary,lat=:1.lat," +
			"lng=:1.lng,stay_time=:1.stayTime,status=:1.status,dest_id=:1.destId,dest_name=:1.destName," +
			"tripdest_id=:1.tripdestId,image=:1.image where location_id=:1.locationId")
	public void updateTripLocation(TripLocationVO location);
	@SQL("select count(*) from tb_tripdest " +
			"#if(:destName != null){ where name like :destName }")
	public int findTripdestCount(@SQLParam("destName")String destName);
	
	@SQL("select * from tb_trip  order by trip_id desc limit :1,:2")
	public List<TripVO> findListTrip(int start,int rows);
	
	@SQL("select * from tb_trip where status = 3 order by trip_id desc limit :1,:2")
	public List<TripVO> findAuditedPageTrips(int startPos,int pageRows);
	
	

	/**
	 * 根据系统目的地的编号查询行程目的地
	 */
	@SQL("select * from tb_tripdest where dest_id = :1")
	public TripdestVO findTripdestByDestId(int destId);
	
	/**
	 * 根据系统的数据ID查询行程地点
	 */
	@SQL("select * from tb_trip_location where data_id = :1")
	public TripLocationVO findTripLocationByDataId(int dataId);
	
	/**
	 * 添加行程
	 * @param trip
	 */
	@ReturnGeneratedKeys
	@SQL("insert into tb_trip (name, summary,price,departure_city,departure_date,book_url,days,image,status,distance) " +
			"values(:1.name,:1.summary,:1.price,:1.departureCity,:1.departureDate,:1.bookUrl,:1.days,:1.image,:1.status,:1.distance)")
	public int addTrip(TripVO trip);
	
	/**
	 * 添加行程与目的地的关系
	 * @param tripTripDests
	 * @return
	 */
	@SQL("insert into tb_trip_tripdests (trip_id,tripdest_id,dest_id,dest_name) " +
			"values(:1.tripId,:1.tripdestId,:1.destId,:1.destName)")
	public void addTripTripdest(TripTripdestsVO tripTripdest);
	
	/**
	 * 添加行程的每天记录
	 * @param daily
	 * @return
	 */
	@ReturnGeneratedKeys
	@SQL("insert into tb_trip_daily (daily_no,trip_id,content,breakfast,lunch,supper,hotel) " +
			"values(:1.dailyNo,:1.tripId,:1.content,:1.breakfast,:1.lunch,:1.supper,:1.hotel)")
	public int addTripDaily(TripDailyVO daily);
	
	/**
	 * 添加每天行程下所要去的地点
	 * @param dailyLoc
	 * @return
	 */
	@ReturnGeneratedKeys
	@SQL("insert into tb_trip_daily_locations (daily_id, trip_id,location_id,location_no,location_name,tripdest_id) " +
			"values(:1.dailyId,:1.tripId,:1.locationId,:1.locationNo,:1.locationName,:1.tripdestId)")
	public int addTripDailyLocation(TripDailyLocationsVO dailyLoc);
	
	@SQL("select t1.*,t2.lat,t2.lng,t2.google_id from tb_trip_tripdests t1 join tb_tripdest t2 on t1.tripdest_id = t2.tripdest_id where trip_id = :1")
	public List<TripTripdestsVO> findTripTripdestsByTripId(int tripId);


	@SQL("select count(trip_id) from tb_trip ")
	public int findTripCount();
	
	@SQL("select count(trip_id) from tb_trip where status =3")
	public int findAuditedTripCount();
	
	
	
	
	/**
	 * 查询审核通过的行程目的地，系统目的地放前面，还根据id倒序
	 * @return
	 */
	@SQL("select * from tb_tripdest where status = 1 order by custom asc, tripdest_id desc")
	public List<TripdestVO> findAuditedTripdests();
	
	@SQL("update tb_trip set name=:1.name,summary=:1.summary,price=:1.price," +
			"departure_city=:1.departureCity,departure_date=:1.departureDate,image=:1.image," +
			"book_url=:1.bookUrl,days=:1.days,distance=:1.distance,status=:1.status where trip_id=:1.tripId")
	public void updateTrip(TripVO trip);
	
	/**
	 * 删除行程关联的行程目的地
	 * @param tripTripdests
	 */
	@SQL("delete from tb_trip_tripdests where trip_tripdest_id = :1.tripTripdestId")
	public void deleteTripTripdest(TripTripdestsVO tripTripdest);
	
	/**
	 * 删除行程关联目的地的地点
	 * @param tripTripdests
	 */
	@SQL("delete from tb_trip_daily_locations where trip_id =:1.tripId and tripdest_id = :1.tripdestId")
	public void deleteTripdestLocations(TripTripdestsVO tripTripdest);

	
	/**
	 * 查询行程目的地
	 * @param googleId
	 * @return
	 */
	@SQL("select * from tb_tripdest where google_id = :1")
	public TripdestVO findTripdestByGoogleId(String googleId);
	
	/**
	 * 查询行程的天列表
	 */
	@SQL("select * from tb_trip_daily where trip_id = :1 order by daily_no asc")
	public List<TripDailyVO> findTripDaily(int tripId);
	
	/**
	 * 查询行程关联的地点
	 * @param tripId
	 * @return
	 */
	@SQL("select t1.*,t2.lat,t2.lng,t2.summary,t2.stay_time,t2.category_id,t2.dest_name,t2.image," +
			"t2.category_name,t2.tripdest_id,t2.dest_name,t2.dest_id,t2.custom,t2.data_id,t2.rel_id " +
			"from tb_trip_daily_locations t1 join tb_trip_location t2 on t1.location_id = t2.location_id " +
			"where t1.trip_id = :1 order by t1.location_no asc")
	public List<TripDailyLocationsVO> findTripDailyocations(int tripId);
//	/**
//	 * 查询行程的关联的地点
//	 * @param tripId
//	 * @return
//	 */
//	@SQL("select t2.* from tb_trip_daily_locations t1 join tb_trip_location t2 on t1.location_id = t2.location_id " +
//	"where t1.trip_id = :1 order by t1.location_no asc")
//	public List<TripLocationVO> findTripLocations(int tripId);
	
	/**
	 * 查询行程目的地下的类别ID列表
	 * @param tripdestId
	 * @return
	 */
	@SQL("select distinct category_id from tb_trip_location where tripdest_id = :1")
	public List<Integer> findTripdestCategoryIdList(int tripdestId);
	
	
	/**
	 * 查询某个目的地下，同类别的地点，并且该地点是审核通过
	 * @param tripdestId
	 * @param categoryId
	 * @return
	 */
	@SQL("select * from tb_trip_location where tripdest_id = :1 and category_id = :2 and status = 1")
	public List<TripLocationVO> findAuditedTripLocations(int tripdestId,int categoryId);
	
	/**
	 * 删除行程关联的地点数
	 * @param locs
	 */
	@SQL("delete from tb_trip_daily_locations where daily_location_id = :1.dailyLocationId")
	public void deleteTripDailyLocations(List<TripDailyLocationsVO> locs);
	
	/**
	 * 删除行程的天
	 * @param dailys
	 */
	@SQL("delete from tb_trip_daily where daily_id = :1.dailyId")
	public void deleteTripDailys(List<TripDailyVO> dailys);
	
	/**
	 * 更新行程天的天编号
	 * @param daily
	 */
	@SQL("update tb_trip_daily set daily_no = :1.dailyNo where daily_id = :1.dailyId")
	public void updateTripDailyDailyNo(TripDailyVO daily);
	
	/**
	 * 更新行行程天下的地点的顺序
	 * @param loc
	 */
	@SQL("update tb_trip_daily_locations set location_no = :1.locationNo where daily_location_id = :1.dailyLocationId")
	public void updateTripDailyLocationLocationNo(TripDailyLocationsVO loc);
	
	/**
	 * 更具google的地点编号获取地点
	 * @param googleId
	 * @return
	 */
	@SQL("select * from tb_trip_location where google_id = :1")
	public TripLocationVO findTripLocationByGoogleId(String googleId);
	
	@SQL("update tb_trip_daily_locations set location_no = :1.locationNo,daily_id = :1.dailyId where daily_location_id = :1.dailyLocationId")
	public void updateLocationNoAndDailyId(TripDailyLocationsVO loc);
	
	/**
	 * 获取行程的第一个目的地
	 * @param tripId
	 * @return
	 */
	@SQL("select * from tb_dest t1 join tb_base_info t2 on t2.data_id=t1.dest_id and t2.info_type=:2 and t2.default_info = 1 " +
			"join tb_image t3 on t3.image_id = t1.image_id " +
			"where t1.dest_id = (select t2.dest_id from tb_trip_tripdests t1 " +
									"join tb_tripdest t2 on t1.tripdest_id = t2.tripdest_id " +
									"where t2.custom =0 and t1.trip_id = :1 limit 0,1)")
	public DestVO findFirstTripdest(int tripId,int infoType);
	
	
	
	/**
	 * 查询行程的航班
	 * @param tripId
	 * @return
	 */
	@SQL("select t1.*,t2.t_icon icon from tb_trip_flight t1 left join tb_friend_item t2 on t1.airline_id = t2.t_friend_item_id " +
			"where t1.trip_id = :1 and t2.t_friend_type_id = 3")
	public List<TripFlightVO> findTripFlights(int tripId);

	/***********************行程编辑接口***start**************************/
	
	@SQL("SELECT * FROM tb_friend_item where t_friend_type_id=3")
	public List<FriendItemVO> findListAirline();
	
//	@SQL("insert into tb_trip(name,summary,price,departure_city,departure_date,book_url," +
//			"days,image,status,distance) values(:1.name,:1.summary,:1.price," +
//			":1.departureCity:1.departureDate,:1.bookUrl,:1.days,:1.image,:1.status,:1.distance)")
//	public void saveTripContent(TripVO tripVO);
	@SQL("insert into  tb_trip_flight(airline_id,airline,flight_no,back,space,departure_city, " +
			"arrive_city,departure_airport,arrive_airport,departure_time,arrive_time,trip_id) " +
			"values(:1.airlineId,:1.airline,:1.flightNo,:1.back,:1.space,:1.departureCity,:1.arriveCity," +
			":1.departureAirport,:1.arriveAirport,:1.departureTime,:1.arriveTime,:1.tripId)")
	public void  saveFlight(List<TripFlightVO> flightVO);
	@ReturnGeneratedKeys
	@SQL("insert into tb_trip_daily(daily_no,trip_id,content,breakfast,lunch,supper,hotel) " +
			"values(:1.dailyNo,:1.tripId,:1.content,:1.breakfast,:1.lunch,:1.supper,:1.hotel)")
	public int saveDaily(TripDailyVO dailyVO);
	
	@SQL("update tb_trip_flight set airline_id=:1.airlineId,airline=:1.airline,flight_no=:1.flightNo,back=:1.back," +
			"space=:1.space,departure_city=:1.departureCity,arrive_city=:1.arriveCity,departure_time=:1.departureTime," +
			" departure_airport=:1.departureAirport,arrive_airport=:1.arriveAirport,arrive_time=:1.arriveTime " +
			" where flight_id=:1.flightId")
	public void  updateFlight(List<TripFlightVO> flightVO);
	@SQL("delete from tb_trip_flight where flight_id=:1")
	public void deleteFlight(int flightId);
	
	@SQL("SELECT * FROM  tb_trip_daily where daily_id=:1")
	public TripDailyVO findTripDailyById(int dailyId);
	
	@SQL("select  t1.daily_location_id,t1.daily_id,t1.location_id,t1.location_name,t2.content,t2.summary,t2.category_id,t2.stay_time,t2.image" +
			" from tb_trip_daily_locations t1 join tb_trip_location t2 on t1.location_id = t2.location_id " +
			" where t1.daily_id=:1 and t2.custom=1")
	public List<TripDailyLocationsVO> findDailyLocations(int dailyId);
	
	@SQL("update tb_trip_daily set content=:1.content,breakfast=:1.breakfast,lunch=:1.lunch," +
			"supper=:1.supper,hotel=:1.hotel where daily_id = :1.dailyId")
	public void updateTripDaily(TripDailyFormVO dailyVO);
	
	@SQL("update tb_trip_location set content=:1.content,summary=:1.summary,stay_time=:1.stayTime," +
			"category_id=:1.categoryId,category_name=:1.categoryName where location_id=:1.locationId")
	public void updateTripLocation(List<TripDailyLocationsVO> list);
	@SQL("update tb_trip_daily_locations set location_name=:1.locationName where daily_location_id=:1.dailyLocationId")
	public void updateDailyLocation(List<TripDailyLocationsVO> list);
	
	@SQL("update tb_trip_location set image=:2 where location_id=:1")
	public void updateTripLocationImage(int locationId,String imageName);
	
	/**
	 * 更新行程的状态
	 */
	@SQL("update tb_trip set status = :2 where trip_id = :1")
	public void updateTripStatus(int tripId,int stauts);
	
	@SQL("update tb_trip set image = :1.image where trip_id=:1.tripId")
	public void updatetTripImage(TripVO trip);
	/***********************行程编辑接口***end**************************/
	@SQL("select * from tb_trip where  departure_date < ( :1 - INTERVAL days DAY) limit :2,:3")
	public List<TripVO> findListExpireTrip(Date date,int start,int rows);
	
	@SQL("select count(*) from tb_trip where  departure_date < ( :1 - INTERVAL days DAY)")
	public int findExpireTripCount(Date date);
	
	@SQL("update tb_trip_tripdests set dest_name=:1.name where tripdest_id=:1.tripdestId")
	public void updateTripTripDest(TripdestVO tripdest);
	
	@SQL("update tb_trip_daily_locations set location_name=:1.name," +
			"tripdest_id=:1.tripdestId where location_id=:1.locationId")
	public void updateTripDailyLocation(TripLocationVO location);
	
	@SQL("select * from tb_dest_traffic2 where dest_id=:1 and level=2 and traffic_type !=5 and traffic_id not in (select data_id from tb_trip_location where dest_id=:1 and category_id =-1)")
	public List<DestTraffic2VO> findUnsyncTraffics(int destId);
	
	@SQL("select * from tb_tripdest")
	public List<TripdestVO> findListTripDests();
	
}
